package com.bw.mapper;

import com.bw.bean.vo;
import com.bw.bean.vo1;
import com.bw.bean.vo2;
import com.bw.bean.vo3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.jmx.export.annotation.ManagedAttribute;

import java.util.List;
@Mapper
public interface vomapper {
    @Select("select bo.org_name'name',\n" +
            "       sum(actual_distance)'amount',\n" +
            "       count(tt.id)'num1',\n" +
            "       sum(timestampdiff(hour ,actual_start_time,actual_end_time))'time',\n" +
            "       sum(timestampdiff(hour ,actual_start_time,actual_end_time))/count(tt.id)'time2',\n" +
            "       sum(actual_distance)/count(tt.id)'amount2'\n" +
            "    from transport_task tt\n" +
            "left join line_base_info lbi on tt.line_id = lbi.id\n" +
            "left join base_organ bo on bo.id = lbi.org_id\n" +
            "group by bo.org_name;")
    List<vo> cx1();
@Select("select b2.name,\n" +
        "    count(sorter_emp_id)'value'\n" +
        "    from transport_task_detail ttd\n" +
        "left join transport_task tt on tt.id = ttd.transport_task_id\n" +
        "left join line_base_info lbi on tt.line_id = lbi.id\n" +
        "left join base_organ bo on lbi.org_id = bo.id\n" +
        "left join base_region_info bri on bo.region_id = bri.id\n" +
        "left join  base_region_info b2 on bri.parent_id = b2.id\n" +
        "group by b2.name;")
    List<vo1> cx2();
@Select("select bd.name,\n" +
        "       count(*)'value'\n" +
        "       from transport_task tt\n" +
        "left join base_dic bd on tt.status = bd.id\n" +
        "group by bd.name\n" +
        "union\n" +
        "select '总订单'name,count(*)'value' from transport_task;")
    List<vo1> cx3();
@Select("select p.name,\n" +
        "       avg(rating)'value'\n" +
        "       from order_info oi\n" +
        "left join order_detail od on oi.id = od.order_info_id\n" +
        "left join product_sku ps on ps.id = od.product_sku_id\n" +
        "left join product_spu p on ps.product_spu_id = p.id\n" +
        "       where p.name is not null\n" +
        "group by p.name;")
    List<vo1> cx4();
@Select("select date(create_time)'name'\n" +
        "     ,count(*)'value'\n" +
        "from order_info group by  date(create_time);")
    List<vo1> cx5();
@Select("select s.type,\n" +
        "    sum(actual_amount)'amount1',\n" +
        "    count(oi.customer_id)'count1',\n" +
        "    count(distinct  oi.customer_id)'count2',\n" +
        "    sum(oi.original_amount)'amount2',\n" +
        "    sum(amount)'amount3'\n" +
        "    from shop s\n" +
        "left join order_info oi on s.id = oi.shop_id\n" +
        "left join payment p on oi.id = p.order_info_id\n" +
        "group by s.type;")
    List<vo2> cx6();
@Select("select ci.chapter_name'name',\n" +
        "       count(user_id)'count1',\n" +
        "       sum(position_sec)'amount1',\n" +
        "       count(distinct user_id)'count2',\n" +
        "       sum(position_sec)/count(user_id)'amount3'\n" +
        "from user_chapter_process ucp\n" +
        "left join chapter_info ci on ci.id = ucp.chapter_id\n" +
        "left join video_info vi on ci.id = vi.chapter_id\n" +
        "group by ci.chapter_name;")
    List<vo3> cx7();
@Select("select '[0,60]'name,sum(score<60)'value' from test_exam\n" +
        "union\n" +
        "select\n" +
        "'[60,70]'name,sum(score>=60 and score<70)'value'from test_exam\n" +
        "union\n" +
        "select\n" +
        "    '[70,80]'name,sum(score>=70 and score<80)'value'from test_exam\n" +
        "union\n" +
        "select\n" +
        "    '[80,90]'name,sum(score>=80 and score<90)'value'from test_exam\n" +
        "union\n" +
        "select\n" +
        "    '[90,100]'name,sum(score>=90 and score<=100)'value'from test_exam")
    List<vo1> cx8();
}

